﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Qianli.DirSir.Common;
using Qianli.DirSir.Model;
using System.Data;
using System.Data.SqlClient;

namespace Qianli.DirSir.DAL
{
    /// <summary>
    /// 采购订单表
    /// </summary>]
    public class StockOrderInfoDAL
    {
        SQLHelper helper = new SQLHelper();
        /// <summary>
        /// 获取供应商信息，填充到下拉列表中
        /// </summary>
        /// <returns></returns>
        public List<ProviderInfo> GetProviderInfo()
        {
            string sql = "select ProviderID,ProviderName from ProviderInfo";
            DataTable dt = helper.ExecDataTable(sql);
            List<ProviderInfo> list = new List<ProviderInfo>();
            foreach (DataRow item in dt.Rows)
            {
                ProviderInfo provider = new ProviderInfo();
                provider.ProviderID = Convert.ToInt32(item["ProviderID"]);
                provider.ProviderName = item["ProviderName"].ToString();
                list.Add(provider);
            }
            return list;
        }
        /// <summary>
        /// 采购订单显示
        /// </summary>
        /// <returns></returns>
        public List<StockOrderInfo> StockOrderInfoShow()
        {
            string sql = "select StockOrderID,ProviderInfo.ProviderName,CityInfo.CityName,StockOrderTime,DocumentsCoding,StockOrderInfo.Remarks,LastModified from ";
            sql += " StockOrderInfo ";
            sql += " join ProviderInfo on StockOrderInfo.ProviderID=ProviderInfo.ProviderID ";
            sql += " join CityInfo on StockOrderInfo.CityID=CityInfo.CityID";
            DataTable dt = helper.ExecDataTable(sql);
            List<StockOrderInfo> list = new List<StockOrderInfo>();
            foreach (DataRow item in dt.Rows)
            {
                StockOrderInfo StockOrderInfo = new StockOrderInfo();
                StockOrderInfo.StockOrderID = Convert.ToInt32(item["StockOrderID"]);
                StockOrderInfo.ProviderName = item["ProviderName"].ToString();
                StockOrderInfo.CityName = item["CityName"].ToString();
                StockOrderInfo.StockOrderTime = item["StockOrderTime"].ToString();
                StockOrderInfo.DocumentsCoding = item["DocumentsCoding"].ToString();
                StockOrderInfo.Remarks = item["Remarks"].ToString();
                StockOrderInfo.LastModified = item["LastModified"].ToString();
                list.Add(StockOrderInfo);
            }
            return list;
        }
        /// <summary>
        /// 采购订单添加
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public List<int> AddStockOrderInfo(StockOrderInfo s, List<PurchaseOrderSheetInfo> listPurchaseOrderSheetInfo)
        {
            if (s.DocumentsCoding == "")
            {
                s.DocumentsCoding = System.Guid.NewGuid().ToString();
            }
            s.StockOrderTime = DateTime.Now.ToShortDateString();
            s.LastModified = DateTime.Now.ToString();
            List<string> cmdTextList = new List<string>();
            List<List<SqlParameter>> cmdParametersList = new List<List<SqlParameter>>();
            StringBuilder strSql = new StringBuilder();
            //Author: JiXiang 20121128
            //Description：添加采购单表结构
            //Modify Begin
            strSql.Append("insert into StockOrderInfo(");
            strSql.Append("ProviderID,CityID,StockOrderTime,DocumentsCoding,Remarks,LastModified)");
            strSql.Append(" values(@ProviderID,@CityID,@StockOrderTime,@DocumentsCoding,@Remarks,@LastModified);select @@identity");
            SqlParameter[] parameters = {
                 new SqlParameter("@ProviderID",SqlDbType.Int),
                 new SqlParameter("@CityID",SqlDbType.Int),
                 new SqlParameter("@StockOrderTime",SqlDbType.Date),
                 new SqlParameter("@DocumentsCoding",SqlDbType.NVarChar,50),
                 new SqlParameter("@Remarks",SqlDbType.NVarChar,20),
                 new SqlParameter("@LastModified",SqlDbType.DateTime),
                                        };
            parameters[0].Value = s.ProviderID;
            parameters[1].Value = s.CityID;
            parameters[2].Value = s.StockOrderTime;
            parameters[3].Value = s.DocumentsCoding;
            parameters[4].Value = s.Remarks;
            parameters[5].Value = s.LastModified;
            //cmdTextList.Add(strSql.ToString());
            //cmdParametersList.Add(parameters.ToList<SqlParameter>());
            //helper.ExecScalar(strSql.ToString(), parameters);
            //Modify End
            int StockOrderID = Convert.ToInt32(helper.ExecScalar(strSql.ToString(), parameters));

            //子表添加数据
            //Author: JiXiang 20120419
            //Modify Begin
            StringBuilder strSqlSub = new StringBuilder();
            strSqlSub.Append("insert into PurchaseOrderSheetInfo(");
            strSqlSub.Append("GoodsID,StockOrderID,GoodsNum,PurchaseOrderSheetRemarks)");
            strSqlSub.Append(" values(@GoodsID,@StockOrderID,@GoodsNum,@PurchaseOrderSheetRemarks)");
            //非空判定
            //Author: ZhaoLiang 22012-04-25
            if (listPurchaseOrderSheetInfo != null)
            {
                foreach (PurchaseOrderSheetInfo m in listPurchaseOrderSheetInfo)
                {
                    m.StockOrderID = StockOrderID;
                    SqlParameter[] parametersSub = {
                         new SqlParameter("@GoodsID", SqlDbType.Int),
                         new SqlParameter("@StockOrderID", SqlDbType.Int),
                         new SqlParameter("@GoodsNum", SqlDbType.Int),
                         new SqlParameter("@PurchaseOrderSheetRemarks", SqlDbType.Int),
                                                       };
                    parametersSub[0].Value = m.GoodsID;
                    parametersSub[1].Value = m.StockOrderID;
                    parametersSub[2].Value = m.GoodsNum;
                    parametersSub[3].Value = m.PurchaseOrderSheetRemarks;
                    //Modify End
                    cmdTextList.Add(strSqlSub.ToString());
                    cmdParametersList.Add(parametersSub.ToList<SqlParameter>());
                }
            }
            return helper.ExecTransaction(cmdTextList, cmdParametersList);
        }
        /// <summary>
        /// 采购订单表删除
        /// </summary>
        /// <returns>删除的时候先判断子表里面是否存在数据，存在执行子表删除，再执行主表删除，否则直接执行主表删除</returns>
        public List<int> DelStockOrderInfo(int StockOrderID)
        {
            List<string> cmdTextList = new List<string>();
            List<List<SqlParameter>> cmdParametersList = new List<List<SqlParameter>>();
            //查看子表里是否有记录
            StringBuilder getstrSql = new StringBuilder();
            getstrSql.Append("select PurchaseOrderSheetID,GoodsName,StockOrderID,GoodsNum,PurchaseOrderSheetRemarks,TypeName ");
            getstrSql.Append(" from PurchaseOrderSheetInfo join GoodsInfo on PurchaseOrderSheetInfo.GoodsID=GoodsInfo.GoodsID");
            getstrSql.Append(" join GoodsTypeInfo on GoodsInfo.TypeID=GoodsTypeInfo.TypeID where StockOrderID=@StockOrderID");

            SqlParameter[] getparametersSub = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
            getparametersSub[0].Value = StockOrderID;

            DataTable dt = helper.ExecDataTable(getstrSql.ToString(), getparametersSub);
            List<PurchaseOrderSheetInfo> list = new List<PurchaseOrderSheetInfo>();
            foreach (DataRow item in dt.Rows)
            {
                PurchaseOrderSheetInfo p = new PurchaseOrderSheetInfo();
                p.PurchaseOrderSheetID = Convert.ToInt32(item["PurchaseOrderSheetID"]);
                p.GoodsName = item["GoodsName"].ToString();
                p.TypeName = item["TypeName"].ToString();
                p.StockOrderID = Convert.ToInt32(item["StockOrderID"]);
                p.GoodsNum = Convert.ToInt32(item["GoodsNum"]);
                p.PurchaseOrderSheetRemarks = item["PurchaseOrderSheetRemarks"].ToString();
                list.Add(p);
            }
            if (list.Count != 0)
            {
                //采购订单子表删除
                StringBuilder strSqlSub = new StringBuilder();
                strSqlSub.Append("delete from PurchaseOrderSheetInfo ");
                strSqlSub.Append("  where StockOrderID=@StockOrderID");

                SqlParameter[] parametersSub = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
                parametersSub[0].Value = StockOrderID;

                cmdTextList.Add(strSqlSub.ToString());
                cmdParametersList.Add(parametersSub.ToList<SqlParameter>());
            }
            //采购订单主表删除
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from StockOrderInfo");
            strSql.Append("  where StockOrderID=@StockOrderID");

            SqlParameter[] SqlParameters = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
            SqlParameters[0].Value = StockOrderID;

            cmdTextList.Add(strSql.ToString());
            cmdParametersList.Add(SqlParameters.ToList<SqlParameter>());

            return helper.ExecTransaction(cmdTextList, cmdParametersList);
        }
        /// <summary>
        /// 查看订单子表详细信息
        /// </summary>
        /// <param name="StockOrderID"></param>
        /// <returns></returns>
        public List<PurchaseOrderSheetInfo> GetlistPurchaseOrderSheetInfoByStockOrderID(int StockOrderID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select PurchaseOrderSheetID,GoodsInfo.GoodsID,GoodsInfo.GoodsCoding,GoodsName,StockOrderID,GoodsNum,PurchaseOrderSheetRemarks ");
            strSql.Append(" from PurchaseOrderSheetInfo join GoodsInfo on PurchaseOrderSheetInfo.GoodsID=GoodsInfo.GoodsID");
            strSql.Append(" where StockOrderID=@StockOrderID");

            SqlParameter[] parametersSub = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
            parametersSub[0].Value = StockOrderID;

            DataTable dt = helper.ExecDataTable(strSql.ToString(), parametersSub);
            List<PurchaseOrderSheetInfo> list = new List<PurchaseOrderSheetInfo>();
            foreach (DataRow item in dt.Rows)
            {
                PurchaseOrderSheetInfo p = new PurchaseOrderSheetInfo();
                p.GoodsID = Convert.ToInt32(item["GoodsID"]);
                p.PurchaseOrderSheetID = Convert.ToInt32(item["PurchaseOrderSheetID"]);
                p.GoodsCoding = item["GoodsCoding"].ToString();
                p.GoodsName = item["GoodsName"].ToString();
                p.TypeName = item["TypeName"].ToString();
                p.StockOrderID = Convert.ToInt32(item["StockOrderID"]);
                p.GoodsNum = Convert.ToInt32(item["GoodsNum"]);
                p.PurchaseOrderSheetRemarks = item["PurchaseOrderSheetRemarks"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 获取采购订单主表信息
        /// </summary>
        /// <param name="StockOrderID"></param>
        /// <returns></returns>
        public StockOrderInfo GetStockOrderByStockOrderID(int StockOrderID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select StockOrderID,ProviderID,CityID,DocumentsCoding,Remarks from StockOrderInfo");
            strSql.Append("where StockOrderID=@StockOrderID ");

            SqlParameter[] parametersSub = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
            parametersSub[0].Value = StockOrderID;
            SqlDataReader reader = helper.ExecReader(strSql.ToString(), parametersSub);
            StockOrderInfo s = new StockOrderInfo();
            while (reader.Read())
            {
                s.StockOrderID = Convert.ToInt32(reader["StockOrderID"]);
                s.ProviderID = Convert.ToInt32(reader["ProviderID"]);
                s.CityID = Convert.ToInt32(reader["CityID"]);
                s.DocumentsCoding = reader["DocumentsCoding"].ToString();
                s.Remarks = reader["Remarks"].ToString();
            }
            return s;
        }
        /// <summary>
        /// 修改采购订单信息
        /// </summary>
        /// <returns></returns>
        public List<int> UpdatePurchaseOrderSheetInfo(StockOrderInfo s, List<PurchaseOrderSheetInfo> listPurchaseOrderSheetInfo)
        {
            s.LastModified = DateTime.Now.ToString();
            List<string> cmdTextList = new List<string>();
            List<List<SqlParameter>> cmdParametersList = new List<List<SqlParameter>>();
            //订单主表最后修改时间修改
            StringBuilder strsql = new StringBuilder();
            strsql.Append("update StockOrderInfo set ");
            strsql.Append("  ProviderID=@ProviderID,CityID=@CityID,Remarks=@Remarks,LastModified=@LastModified where StockOrderID=@StockOrderID");
            SqlParameter[] updsqlparmeter ={
                                             new SqlParameter("@ProviderID",SqlDbType.Int),
                                             new SqlParameter("@CityID",SqlDbType.Int),
                                             new SqlParameter("@Remarks",SqlDbType.NVarChar,20),
                                             new SqlParameter("@LastModified",SqlDbType.DateTime),
                                             new SqlParameter("@StockOrderID",SqlDbType.Int)
                                         };
            updsqlparmeter[0].Value = s.ProviderID;
            updsqlparmeter[1].Value = s.CityID;
            updsqlparmeter[2].Value = s.Remarks;
            updsqlparmeter[3].Value = s.LastModified;
            updsqlparmeter[4].Value = s.StockOrderID;
            cmdTextList.Add(strsql.ToString());
            cmdParametersList.Add(updsqlparmeter.ToList<SqlParameter>());

            //采购订单子表删除
            StringBuilder delstrSqlSub = new StringBuilder();
            delstrSqlSub.Append("delete from PurchaseOrderSheetInfo ");
            delstrSqlSub.Append("  where StockOrderID=@StockOrderID");

            SqlParameter[] delparametersSub = { 
                                         new SqlParameter("@StockOrderID",SqlDbType.Int)  
                                           };
            delparametersSub[0].Value = s.StockOrderID;

            cmdTextList.Add(delstrSqlSub.ToString());
            cmdParametersList.Add(delparametersSub.ToList<SqlParameter>());
            //采购订单子表重填数据
            //Author: JiXiang 20120419
            //Modify Begin
            StringBuilder addstrSqlSub = new StringBuilder();
            addstrSqlSub.Append("insert into PurchaseOrderSheetInfo(");
            addstrSqlSub.Append(" GoodsID,StockOrderID,GoodsNum,PurchaseOrderSheetRemarks)");
            addstrSqlSub.Append(" values(@GoodsID,@StockOrderID,@GoodsNum,@PurchaseOrderSheetRemarks)");
            //非空判定
            //Author: ZhaoLiang 22012-04-25
            if (listPurchaseOrderSheetInfo != null)
            {
                foreach (PurchaseOrderSheetInfo m in listPurchaseOrderSheetInfo)
                {
                    m.StockOrderID = s.StockOrderID;
                    SqlParameter[] addparametersSub = {
                         new SqlParameter("@GoodsID", SqlDbType.Int),
                         new SqlParameter("@StockOrderID", SqlDbType.Int),
                         new SqlParameter("@GoodsNum", SqlDbType.Int),
                         new SqlParameter("@PurchaseOrderSheetRemarks", SqlDbType.Int),
                                                       };
                    addparametersSub[0].Value = m.GoodsID;
                    addparametersSub[1].Value = m.StockOrderID;
                    addparametersSub[2].Value = m.GoodsNum;
                    addparametersSub[3].Value = m.PurchaseOrderSheetRemarks;
                    //Modify End
                    cmdTextList.Add(addstrSqlSub.ToString());
                    cmdParametersList.Add(addparametersSub.ToList<SqlParameter>());
                }
            }
            return helper.ExecTransaction(cmdTextList, cmdParametersList);
        }
        /// <summary>
        /// 根据采购订单模糊查询
        /// </summary>
        /// <param name="StockOrderName"></param>
        /// <returns></returns>
        public List<StockOrderInfo> GetStockOrderByStockOrderName(string DocumentsCoding)
        {
            StringBuilder StrSql = new StringBuilder();
            StrSql.Append("select StockOrderID,ProviderInfo.ProviderName,CityInfo.CityName,StockOrderTime,DocumentsCoding,StockOrderInfo.Remarks,LastModified from StockOrderInfo");
            StrSql.Append("  join ProviderInfo on StockOrderInfo.ProviderID=ProviderInfo.ProviderID ");
            StrSql.Append("  join CityInfo on StockOrderInfo.CityID=CityInfo.CityID");
            StrSql.Append("  where DocumentsCoding like '%@DocumentsCoding%'");
            SqlParameter[] parametersSub = { 
                                         new SqlParameter("@DocumentsCoding",SqlDbType.NVarChar,50)  
                                           };
            parametersSub[0].Value = DocumentsCoding;
            DataTable dt = helper.ExecDataTable(StrSql.ToString(), parametersSub);
            List<StockOrderInfo> list = new List<StockOrderInfo>();
            foreach (DataRow item in dt.Rows)
            {
                StockOrderInfo s = new StockOrderInfo();
                s.StockOrderID = Convert.ToInt32(item["StockOrderID"]);
                s.ProviderName = item["ProviderName"].ToString();
                s.CityName = item["CityName"].ToString();
                s.StockOrderTime = item["StockOrderTime"].ToString();
                s.DocumentsCoding = item["DocumentsCoding"].ToString();
                s.Remarks = item["Remarks"].ToString();
                s.LastModified = item["LastModified"].ToString();
                list.Add(s);
            }
            return list;
        }
    }
}